# -*- coding: utf-8 -*-
import ConfigParser
import urllib2,re,os,sqlite3
from gzip import GzipFile
from StringIO import StringIO
from datetime import datetime,timedelta
from win32com.client import Dispatch
from pyPdf import PdfFileWriter, PdfFileReader

import sys,getopt



class ContentEncodingProcessor(urllib2.BaseHandler):
    """A handler to add gzip capabilities to urllib2 requests """

    # add headers to requests
    def http_request(self, req):
        req.add_header("Accept-Encoding", "gzip, deflate")
        return req

    # decode
    def http_response(self, req, resp):
        old_resp = resp
        # gzip
        if resp.headers.get("content-encoding") == "gzip":
            gz = GzipFile(
                fileobj=StringIO(resp.read()),
                mode="r"
            )
            resp = urllib2.addinfourl(gz, old_resp.headers, old_resp.url, old_resp.code)
            resp.msg = old_resp.msg
        # deflate
        if resp.headers.get("content-encoding") == "deflate":
            gz = StringIO( deflate(resp.read()) )
            resp = urllib2.addinfourl(gz, old_resp.headers, old_resp.url, old_resp.code)  # 'class to add info() and
            resp.msg = old_resp.msg
        return resp

# deflate support
import zlib
def deflate(data):   # zlib only provides the zlib compress format, not the deflate format;
    try:               # so on top of all there's this workaround:
        return zlib.decompress(data, -zlib.MAX_WBITS)
    except zlib.error:
        return zlib.decompress(data)








# 去除list中重复的值
def f9(seq):
    # Not order preserving
    return {}.fromkeys(seq).keys()
        
# 判断是否是进口
def hasimport(cd):
    # 第8位为0是出口，1是进口
    return int(cd[8])
    
# 定义获取已经存在的报关单号的list函数
# 由于海关报关系统中的单子修改后日期会变更，查询当天时会把前几天的单子也查询出来，因此更改查询条件为报关单号是否存在
def getexistcdnos(cds):
    has_cdnos=[]
    # 加逗号和单引号分隔符
    cdnos_str="','".join(cds)
    # 前后各加上1个单引号，组成sql中in条件中的字符串
    cdnos_str="'"+cdnos_str+"'"
    print u"查询字符串：",cdnos_str
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_synmode=1 and cd_no in ("+cdnos_str+")")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

def gettodaycdnos(search_date):
    has_cdnos=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd_no from cd where cd_date=#"+search_date+"#")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        has_cdnos.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return has_cdnos

    
# 获取委托人关联的模板费用，默认为托浦1
def gettmpfee():
    wtr='1'
    tmp={}
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select receive,pay from wtr left join tmp on wtr.tmp=tmp.id where wtr.id="+wtr)
    if not oRS.EOF:
        if oRS.Fields("receive").Value:
            tmp["cd_receive"]=str(oRS.Fields("receive").Value)
        else:
            tmp["cd_receive"]='0'
        if oRS.Fields("pay").Value:
            tmp["cd_pay"]=str(oRS.Fields("pay").Value)
        else:
            tmp["cd_pay"]='0'
    else:
        tmp["cd_receive"]='0'
        tmp["cd_pay"]='0'
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return tmp

def cleanhtml(html):
    pattern='\s+|(&nbsp;)+'
    # pattern='\s+'
    html=re.sub(pattern,' ',html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <','><')



def searchcdpass(cd_no):
    url='http://www.npedi.com/edi/passingInfoAction.do?passno=%s'%cd_no
    print url
    content=urllib2.urlopen(url).read()
    #    f=open(r'file\\'+cd_no+r'.'+str(sign)+r'.html','w')
    #    f.write(content)
    #    f.close()
#    print content
    cd_checks=re.findall('<TD height="24" class="dblue12">.+?</TD>',cleanhtml(content))
    print cd_checks
    if cd_checks:
        passtime=re.sub(r'<.+?>','',cd_checks[6])
        print u'查验：',passtime
        return passtime
#    if cd_checks:
#        oConn = Dispatch('ADODB.Connection')
#        oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
#        oConn.Open()
#        oCmd = Dispatch('ADODB.Command')
#        oCmd.ActiveConnection = oConn
#        oCmd.CommandText = 'update cd set cd_check=1 where cd_no in (%s)'%','.join(cd_checks)
#        print oCmd.CommandText
#
#        oCmd.Execute()
#        oCmd = None
#        oConn.Close()
#        oConn = None

def updatecdpass():
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select cd.id,cd_no from cdpass inner join cd on cdpass.cd=cd.id where cdpass.pass=0")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        passtime=searchcdpass(oRS.Fields("cd_no").Value)
        if passtime:
            oCmd.CommandText = "update cdpass set pass=1,passtime='%s' where cd = %s"%(passtime,oRS.Fields("id").Value)
            print oCmd.CommandText

            oCmd.Execute()
        oRS.MoveNext()
        # print u"查找到的报关单号：",has_cdnos
    oCmd = None
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None



# 新增一个报关单
def addnewcdno(cd):
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    cd_bwtr=str(cd['cd_bwtr'])
    cd_no=cd['cd_no']
    # 进口数据库设置为18，出口为16
    if hasimport(cd_no):
        category='18'
    else:
        category='16'
    cd_company=cd['cd_company']
    cd_voyage=cd['cd_voyage']
    if cd['cd_lading']:
        cd_lading=cd['cd_lading']
    else:
        cd_lading=''
    cd_verify=cd['cd_verify']
    cd_passno=cd['cd_passno']
    cd_orderno=cd['cd_orderno']
    ic_code=cd['ic_code']
    input_iccode=cd['input_iccode']
    tmpfee=gettmpfee()
    cd_receive=tmpfee['cd_receive']
    cd_pay=tmpfee['cd_pay']
    try:
        cd_boxno=str(int(cd['cd_boxno']))
    except:
        cd_boxno='0'
    cd_goodscount=str(int(cd['cd_goodscount']))
    cd_entrytype=cd['cd_entrytype']
    # cd_memo=cd['cd_memo']
    oCmd.CommandText = "insert into cd(wtr,bwtr,category,cd_no,cd_date,cd_settledate,cd_company,cd_voyage,cd_lading,cd_verify,cd_post,username,cd_passno,cd_orderno,cd_receive,cd_pay,cd_boxno,ic_code,cd_goodscount,input_iccode,cd_entrytype) values(1,"+cd_bwtr+","+category+",'"+cd_no+"','"+search_date+"','"+search_date+"','"+cd_company+"','"+cd_voyage+"','"+cd_lading+"','"+cd_verify+"',1,1,'"+cd_passno+"','"+cd_orderno+"',"+cd_receive+","+cd_pay+",'"+cd_boxno+"','"+ic_code+"',"+cd_goodscount+",'"+input_iccode+"','"+cd_entrytype+"')"
    print oCmd.CommandText
    
    oCmd.Execute()
    
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn
    oRS.Open("select @@IDENTITY")
    re_id=oRS.getstring
    oRS.Close()
    oRS=None
    
    # searchcdpass(cd['cd_no'])
    # oCmd.CommandText = "insert into cdpass(cd) values("+re_id+")"
    # oCmd.Execute()
    
    boxs=cd['boxs']
    print boxs
    for box in boxs:
        oCmd.CommandText = "insert into cdbox(cd,box) values("+re_id+",'"+box+"')"
        oCmd.Execute()
    oCmd.CommandText = "insert into record(re_table,re_id,re_operate,re_memo,username) values('cd',"+re_id+",'"+unicode('新增','utf8')+"','"+unicode('同步','utf8')+"',1)"
    oCmd.Execute()
    oCmd = None
    oConn.Close()
    oConn = None
    global UPDATECDS
    UPDATECDS=UPDATECDS+1
    return re_id


    
def updatelog(cdcount,updatecount):
    updatetime=datetime.now().strftime("%H:%M:%S")
    if cdcount:
        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = MDBConnString
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        ul_cont=u"系统同步信息："+updatetime+u" 同步来自宁波海关QP报关单预录入系统 "+str(cdcount)+u" 单，系统新增 "+str(updatecount)+u" 条报关单"
        oCmd.CommandText="insert into ul(ul_date,ul_post,ul_cont) values('"+search_date+"',1,'"+ul_cont+"')"
        oCmd.Execute()
        oCmd = None
        oConn.Close()
        oConn = None
    


def getbwtr():
    bwtrs=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select bwtr.id,bwtr.bwtr,bwtric.* from bwtr,bwtric where bwtr.id=bwtric.bwtr")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("ic_code").Value:
                bwtr={}
                # print oRS.Fields("cd_no").Value
                bwtr["id"]=oRS.Fields("bwtr.id").Value
                bwtr["bwtr"]=oRS.Fields("bwtr.bwtr").Value
                bwtr["ic_code"]=oRS.Fields("ic_code").Value
                bwtr["decno"]=oRS.Fields("decno").Value
                bwtrs.append(bwtr)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    return bwtrs

def searchboxcheck(querydatelist):
    boxs=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oCmd = Dispatch('ADODB.Command')
    oCmd.ActiveConnection = oConn
    sql="select * from boxcheck where [check]=False and cd_date in (#%s#)"%'#,#'.join(querydatelist)
    print sql
    oRS.Open(sql)                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("box").Value:
            box={}
            box["cd_id"]=oRS.Fields("cd_id").Value
            box["cd_no"]=oRS.Fields("cd_no").Value
            box["cd_date"]=datetime.fromtimestamp(int(oRS.Fields("cd_date").Value)).strftime('%Y-%m-%d')
            box["box_id"]=oRS.Fields("box_id").Value
            box["box"]=oRS.Fields("box").Value
            box["cd_voyage"]=oRS.Fields("cd_voyage").Value
            boxs.append(box)
        oRS.MoveNext()
    oRS.Close()
    oRS=None
    # print boxs

    def filtervessel(boxs):
        vessels=[]
        for box in boxs:
            if not box['cd_voyage'] in vessels:
                vessels.append(box['cd_voyage'])
        return vessels

    def filterbox(boxs):
        fboxs=[]
        for box in boxs:
            fbox={}
            fbox['box']=box['box']
            fbox['cd_date']=box['cd_date']
            fbox['cd_voyage']=box['cd_voyage']
            if not fbox in fboxs:
                fboxs.append(fbox)
        return fboxs

    print 'len(boxs):%s'%len(boxs)
    connnpedi=sqlite3.connect(NPEDIDB3)
    connnpedi.text_factory = str
    connnpedi.row_factory = sqlite3.Row
    for box in boxs:
        sql="select * from cusmov where boxno='%s' and vessel='%s' and voyage='%s' and instructtime>='%s'"%(box['box'],box['cd_voyage'].split('/')[0],box['cd_voyage'].split('/')[1],box['cd_date'])
        # print sql
        if connnpedi.execute(sql).fetchall():
            oCmd.CommandText = "update boxcheck set cd_check=True,[check]=True where [check]=False and box='%s' and cd_date=#%s# and cd_voyage='%s'"%(box['box'],box['cd_date'],box['cd_voyage'])
            print oCmd.CommandText
            oCmd.Execute()
    connnpedi.close()
    oCmd = None
    oConn.Close()
    oConn=None
    
def searchunrelease(decno):
    url='www.nbedi.com/nbeport_h2000pd/HisSearch/UnReleaseExport.aspx?g=%s&JumpPage=0'%decno
#    url='http://www.nbedi.com/nbeport_h2000pd/HisSearch/ReleaseExport.aspx?DECL_CO=31002155'
    print url
    content=urllib2.urlopen(url).read()
#    f=open(r'file\\'+cd_no+r'.'+str(sign)+r'.html','w')
#    f.write(content)
#    f.close()
    cd_checks=re.findall('\d{18}',content,re.M)
    print u'查验：',cd_checks
    if cd_checks:
        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = MDBConnString
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        oCmd.CommandText = 'update cd set cd_check=1 where cd_no in (%s)'%','.join(cd_checks)
        print oCmd.CommandText

        oCmd.Execute()
        oCmd = None
        oConn.Close()
        oConn = None

def gen_oldfile(conn):
    s=[x[0] for x in conn.execute("select seq_no from dec_mess_status where typist_no='6300000006981' and notice_date>'2012-05-05' ").fetchall()]
    for seq_no in s:
        htmlfile.gen_cdfile(seq_no,conn)
        
def gen_querydate(datecount):
    querydatelist=[]
    queryday=datetime.today()
    for c in xrange(datecount):
        querydatelist.append(datetime.strftime(queryday-timedelta(days=c),'%Y-%m-%d'))
    return querydatelist


def gen_pdffile(cdno):
    pdflist=[]
    for x in xrange(1,6):
        if not os.path.isfile('file\html\%s.%s.html'%(cdno,x)):
            break
        else:
            print 'file\html\%s.%s.html'%(cdno,x)
            if not os.path.exists('file\pdf'):
                os.makedirs('file\pdf')
            os.popen('%s --zoom 2 file\html\%s.%s.html file\pdf\%s.%s.pdf'%(wkhtmltopdf,cdno,x,cdno,x))
            pdflist.append('file\pdf\%s.%s.pdf'%(cdno,x))
    if os.path.isfile('file\html\%s.box.html'%cdno):
        os.popen('%s --zoom 2 file\html\%s.box.html file\pdf\%s.box.pdf'%(wkhtmltopdf,cdno,cdno))
        pdflist.append('file\pdf\%s.box.pdf'%cdno)
    output = PdfFileWriter()
    for pdf in pdflist :
        inpdf = PdfFileReader(file(pdf, "rb"))
        output.addPage(inpdf.getPage(0))

    # finally, write "output" to document-output.pdf
    outputStream = file('file\pdf\%s.pdf'%cdno, "wb")
    output.write(outputStream)
    outputStream.close()
    
    
def gen_allpdffile():
    cd_list=[]
    oConn = Dispatch('ADODB.Connection')
    oConn.ConnectionString = MDBConnString
    oConn.Open()
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn        # Set the recordset to connect thru oConn
    oRS.Open("select distinct cd_no from cd")                         # Open a table called zipcodeoCmd = Dispatch('ADODB.Command')
    while not oRS.EOF:
        if oRS.Fields("cd_no").Value:
                cd_list.append(oRS.Fields("cd_no").Value)
        oRS.MoveNext()
    # print u"查找到的报关单号：",has_cdnos
    oRS.Close()
    oRS=None
    oConn.Close()
    oConn=None
    for cdno in cd_list:
        gen_pdffile(cdno)


def ReadConfig():
    try:
        config = ConfigParser.ConfigParser()
        config.read('config\qpspider.ini')
#        NetCard=config.get("Default","NetCard")
        CorpID=config.get("Default","corpid")
        CorpName=config.get("Default","corpname").decode('gb2312')
        ServerIP=config.get("Default","serverip")
        ServerPort=config.get("Default","serverport")
        AppPath=config.get("Default","apppath")
        QP4Path=config.get("Default","qp4path")
        NPEDIPath=config.get("Default","npedipath")
        #        print NetCard,CorpID,CorpName
#        return NetCard,CorpID,CorpName,ServerIP,ServerPort
        print CorpID,CorpName
        return CorpID,CorpName,ServerIP,ServerPort,AppPath,QP4Path,NPEDIPath
    except:
        print u'读取配置文件错误，或配置文件不存在'.encode('gbk')
#        sys.exit(1)

def CheckStatus(corpid,corpname):
    copconn=sqlite3.connect(COPDB3)
    copconn.text_factory = str
    copconn.row_factory=sqlite3.Row
    status=copconn.execute("select vaild_status from bg where id=? and cop_name=?",(corpid,corpname)).fetchone()
    copconn.close()
    print status
    if status:
        return status['vaild_status']

def GetCasCookie(corpid):
    copconn=sqlite3.connect(COPDB3)
    copconn.text_factory = str
    copconn.row_factory=sqlite3.Row
    row=copconn.execute("select cas_cookie from bg where id=?",(corpid,)).fetchone()
    copconn.close()
    print row
    if row:
        return row['cas_cookie']
        
def WriteStatusToHTML(QPStatus):
    hf=open('%s\QPStatus.HTML'%LibPath,'w')
    if QPStatus==1:
        s=''
    else:
        s=u'<div style="color:red">提示：QP数据可能未导入</div>'.encode('utf-8')
    hf.write(s)




def GetEdocFile(edocs):

    print u'下载随附单据 %s'%len(edocs)
    cookie=edocfile.EdocLogin(CasCookie)
    if edocs and cookie:

        oConn = Dispatch('ADODB.Connection')
        oConn.ConnectionString = MDBConnString
        oConn.Open()
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn

        def AddNewEdoc(edoc):
            if edoc['transfer_name'] is not None:
                edoc['transfer_name']=edoc['transfer_name'].decode('utf-8')
            else:
                edoc['transfer_name']=''
            if edoc['edoc_owner_name'] is not None:
                edoc['edoc_owner_name']=edoc['edoc_owner_name'].decode('utf-8')
            else:
                edoc['edoc_owner_name']=''
            oCmd.CommandText = "insert into cdedoc(cd,edoc_id,edoc_size,transfer_iccard,transfer_name,add_time,edoc_owner_code,edoc_owner_name) values(%s,'%s',%s,'%s','%s','%s','%s','%s')"%(edoc['cd'],edoc['edoc_id'],edoc['edoc_size'],edoc['transfer_iccard'],edoc['transfer_name'],datetime.strftime(datetime.strptime(edoc['add_time'],'%Y-%m-%dT%H:%M:%S+08:00'),'%Y-%m-%d %H:%M:%S'),edoc['edoc_owner_code'],edoc['edoc_owner_name'])
            print oCmd.CommandText

            oCmd.Execute()

        def DownPDF(edoc):
            pdf=edocfile.GetPDF(cookie,EDOCFilePath,edoc['edoc_id'])
            i=0
            while not pdf:
                if i==10:
                    print u'尝试次数过多，无法下载'
                    return False
                i=i+1
                time.sleep(i)
                print u'尝试第 %s 次'%i
                pdf=edocfile.GetPDF(cookie,EDOCFilePath,edoc['edoc_id'])
            AddNewEdoc(edoc)

        for edoc in edocs:

            DownPDF(edoc)

        oCmd = None
        oConn.Close()
        oConn = None

def query_db(query, args=(), one=False):
    # print query
    cur = conn.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv
    
    
def ProcessArgv(argv):
    querydatecount=4
    # 定义查询的日期
    # search_date=datetime.now().strftime("%Y-%m-%d")
    #    search_date='2012-05-25'
    # querydatecount=4
    querydatelist=gen_querydate(querydatecount)
    
    
    
    try:
        opts,args=getopt.getopt(argv,'hc:d:')
    except:
        print 'sys.argv error'
        sys.exit(2)
        
    for opt,arg in opts:
        if opt=='-c':
            try:
                t_count=int(arg)
                if t_count>0 and t_count<=60:
                    querydatecount=t_count
                    querydatelist=gen_querydate(querydatecount)
            except:
                print '-c error'
                pass
        elif opt=='-d':
            try:
                d_date=datetime.strptime(arg,'%Y-%m-%d')
                querydatelist=[]
                querydatelist.append(d_date.strftime('%Y-%m-%d'))
            except:
                print '-d error'

    print querydatecount,querydatelist
    return querydatelist
    
# ##########################################
# 主程序开始
# ##########################################
if __name__=="__main__":
    print sys.argv
    querydatelist=ProcessArgv(sys.argv[1:])

    
    

    CorpID,CorpName,ServerIP,ServerPort,AppPath,QP4Path,NPEDIPath=ReadConfig()
    LibPath='%s\\lib'%AppPath
    DataBasePath='%s\\data'%AppPath
    HTMLFilePath='%s\\file\\html'%AppPath
    PDFFilePath='%s\\file\\pdf'%AppPath
    EDOCFilePath='%s\\file\\edoc'%AppPath
    QPDB3='%s\\qp.db3'%QP4Path
    COPDB3='%s\\cop.db3'%QP4Path
    NPEDIDB3='%s\\npedi.db3'%NPEDIPath
    wkhtmltopdf='%s\\wkhtmltopdf\\wkhtmltopdf.exe'%QP4Path
    WebServer='http://%s:%s'%(ServerIP,ServerPort)
    MDBConnString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s\\data.mdb;Jet OLEDB:Database Password=tp@chenkai'%DataBasePath

    sys.path.append(QP4Path)
    import htmlfile,edocfile



    QPStatus=CheckStatus(CorpID,CorpName)
    CasCookie=GetCasCookie(CorpID)
    #QPStatus=0
    WriteStatusToHTML(QPStatus)


    conn=sqlite3.connect(QPDB3)
    conn.text_factory = str
    conn.row_factory = sqlite3.Row

    bwtrs=getbwtr()
    print bwtrs
    ic_code=[]
    for bwtr in bwtrs:
        ic_code.append("'%s'"%bwtr['ic_code'])
    typist_no=','.join(ic_code)


    print u'开始对被委托人',CorpName,typist_no,u'抓取数据'
    #    bwtr['ic_code']='8320000008705'



    edocs=[]

    for search_date in querydatelist:

        # 定义更新的报关单数量
        global UPDATECDS
        UPDATECDS=0
        # sql='select seq_no,entry_id from dec_mess_status where status=\'E\' and typist_no=\'%s\' and notice_date like \'%s%%\''%(bwtr['ic_code'],search_date)
        # 新增无纸化报关种类，status的代码是P，无纸报关遇到查验，状态代码是Q
        sql="select seq_no,entry_id from dec_mess_status where status in ('E','P','Q') and typist_no in (%s) and notice_date like '%s%%'"%(typist_no,search_date)
        print sql
        search_cdnos=conn.execute(sql).fetchall()
        # print search_cdnos,len(search_cdnos)
        cds=[]
        [cds.append(x[1]) for x in search_cdnos]
        print cds,len(cds)
        new_cdnos=list(set(cds)-set(getexistcdnos(cds)))
        print search_date,u"新产生的报关单号：",len(new_cdnos),new_cdnos
        # print u"手工添加的报关单号：",list(set(gettodaycdnos(search_date))-set(cds))
        new_seqno=[]
        for x in search_cdnos:
            if x[1] in new_cdnos:
                new_seqno.append(x[0])
        print new_seqno
        for seq in new_seqno:
            cd={}
            head=conn.execute('select dec_head.*,dec_freetxt.voyno from dec_head,dec_freetxt where dec_head.seq_no=dec_freetxt.seq_no and dec_head.seq_no=\'%s\''%seq).fetchone()
            mess=conn.execute('select typist_no from dec_mess_status where seq_no=\'%s\''%seq).fetchone()
            # print head
            # 被委托人默认1
            # cd['cd_bwtr']=bwtr['id']
            cd['cd_bwtr']=1
            print u"统一编号：",head['seq_no']
            cd["seq_no"]=head['seq_no']
            print u"报关单号：",head['entry_id']
            cd["cd_no"]=head['entry_id']
            print u"企业名称：",head['trade_name'].decode('utf-8')
            cd["cd_company"]=head['trade_name'].decode('utf-8')
            print u"船名航次：%s/%s"%(head['traf_name'],head['voyno'])
            if head['traf_name'] is None:
                traf_name=''
            else:
                traf_name=head['traf_name']
            if head['voyno'] is None:
                voyno=''
            else:
                voyno=head['voyno']
            cd['cd_voyage']='%s/%s'%(traf_name,voyno)
            print u"核销单号：",head['appr_no']
            if head['appr_no']:
                cd["cd_verify"]=head['appr_no']
            else:
                cd["cd_verify"]=''
            print u"提单号：",head['bill_no']
            if head['bill_no']:
                cd["cd_lading"]=head['bill_no'].decode('utf-8')
            else:
                cd["cd_lading"]=''
            cd_passno=conn.execute('select cert_code from dec_licensedocu where seq_no=\'%s\' and (docu_code=\'B\' or docu_code=\'A\')'%seq).fetchone()
            print u"通关单号：",cd_passno
            if cd_passno:
                cd['cd_passno']=cd_passno[0]
            else:
                cd['cd_passno']=''
            print u"订单号：",head['contr_no']
            if head['contr_no']:
                cd['cd_orderno']=head['contr_no'].decode('utf-8')
            else:
                cd['cd_orderno']=''
            print u'集装箱数量',head['jzxsl']
            if head['jzxsl']:
                cd['cd_boxno']=head['jzxsl']
            else:
                cd['cd_boxno']=''
            cd['cd_goodscount']=head['items_no']
    #        dec_head里面的typist_no是按照输单的ic卡号
    #        dec_mess_status里面的typist_no是按照发送的时候ic卡号
    #        现在要求分组以发送的时候ic卡号为准，因此修改
    #        cd['ic_code']=head['typist_no']
            cd['ic_code']=mess['typist_no']
            cd['input_iccode']=head['typist_no']
            cd['cd_entrytype']=head['entry_type']

            # boxs=[]
            # [boxs.append(re.sub(r'<.+?>','',x)) for x in re.findall(pattern,boxhtml)]
            # cd['boxs']=boxs
            # addnewcdno(cd)
            cd_boxs=conn.execute('select container_id from dec_container where seq_no=\'%s\''%seq).fetchall()
            cd['boxs']=[]
            [cd['boxs'].append(box[0]) for box in cd_boxs]
            print cd['boxs']

            htmlfile.gen_cdfile(cd['seq_no'],conn)
            gen_pdffile(cd['cd_no'])


            re_id=addnewcdno(cd)

            # cd_edocs=[]
            # [cd_edocs.append(edoc[0]) for edoc in conn.execute("select edoc_id from dec_edocs_realation where seq_no='%s'"%seq).fetchall()]
            # print cd_edocs
            # edoc_dict={}
            # edoc_dict[re_id]=cd_edocs

            edocs.extend(query_db("select %s as cd,edoc_id,edoc_size,transfer_iccard,transfer_name,add_time,edoc_owner_code,edoc_owner_name from dec_edocs_realation where seq_no='%s'"%(re_id,seq)))

            # searchunrelease(bwtr['decno'])
            print 'UPDATECDS:%s'%UPDATECDS
        updatelog(len(new_cdnos),UPDATECDS)
    searchboxcheck(querydatelist)
    # updatecdpass()
    # gen_oldfile(conn)
    # gen_allpdffile()
    GetEdocFile(edocs)
    conn.close()